﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
using Ionic.Zip;
using System.IO;

namespace SalesReportReading
{
    public class Program
    {
        private static string GetTemporaryDirectory()
        {
            string tempDir = Path.Combine(
                Path.GetTempPath(),
                Path.GetRandomFileName());

            Directory.CreateDirectory(tempDir);

            return tempDir;
        }

        private static void MyExtract()
        {
            string zipToUnpack = "..\\..\\Sales-Report.zip";
            string unpackDirectory = GetTemporaryDirectory();
            using (ZipFile zip = ZipFile.Read(zipToUnpack))
            {
                foreach (ZipEntry e in zip)
                {
                    e.Extract(unpackDirectory, ExtractExistingFileAction.OverwriteSilently);
                }
            }

            RecursiveFileProcessor(unpackDirectory);
        }

        public static void RecursiveFileProcessor(string path)
        {
            if (File.Exists(path))
            {
                ProcessFile(path);
            }
            else if (Directory.Exists(path))
            {
                ProcessDirectory(path);
            }
            else
            {
                Console.WriteLine("{0} is not a valid file or directory.", path);
            }
        }

        public static void ProcessDirectory(string dir)
        {
            string[] fileEntries = Directory.GetFiles(dir);
            foreach (string fileName in fileEntries)
            {
                ProcessFile(fileName);
            }

            // Recurse into subdirectories
            string[] subdirectoryEntries = Directory.GetDirectories(dir);
            foreach (string subdirectory in subdirectoryEntries)
            {
                ProcessDirectory(subdirectory);
            }
        }

        private static string GetConnectionString(string filePath)
        {
            try
            {
                var sbConnection = new OleDbConnectionStringBuilder();
                var strExtendedProperties = string.Empty;
                sbConnection.DataSource = filePath;

                if (Path.GetExtension(filePath).Equals(".xls")) // for 97-03 Excel file
                {
                    sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
                    strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1"; // HDR = ColumnHeader, IMEX = InterMixed
                }
                else if (Path.GetExtension(filePath).Equals(".xlsx")) // for 2007 Excel file
                {
                    sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
                    strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
                }

                sbConnection.Add("Extended Properties", strExtendedProperties);

                return sbConnection.ToString();
            }
            catch (Exception)
            {
                throw new ArgumentException("Such path does not exist!");
            }
        }

        public static void ProcessFile(string path)
        {
            DataTable sheet = new DataTable("Sales");

            var dbConnString = GetConnectionString(path);
            var dbConnection = new OleDbConnection(dbConnString);
            dbConnection.Open();
            using (dbConnection)
            {
                var command = new OleDbCommand("SELECT * FROM [Sales$];", dbConnection);

                using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                {
                    adapter.FillSchema(sheet, SchemaType.Source);
                    adapter.Fill(sheet);
                }

                var reader = command.ExecuteReader();
                int rowsCount = sheet.Rows.Count - 1;
                string location = path.Substring(path.LastIndexOf('\\') + 1);

                for (int i = 0; i < rowsCount; i++)
                {
                    var productId = int.Parse(sheet.Rows[i].ItemArray[0].ToString());
                    var quantity = int.Parse(sheet.Rows[i].ItemArray[1].ToString());
                    var unitPrice = decimal.Parse(sheet.Rows[i].ItemArray[2].ToString());
                    var sum = decimal.Parse(sheet.Rows[i].ItemArray[3].ToString());
                    //Console.WriteLine("{0}\t{1}\t{2}\t{3}",
                    //    productId, quantity, unitPrice, sum);
                }
            }
        }


        public static void Main()
        {
            MyExtract();
        }
    }
}
